using SPI.AgentServer.Services.Execution;
using System.Text;

namespace SPI.AgentServer.Services.Tools;

/// <summary>
/// 人员进出查询工具 - 查询人员通过闸机的进出记录
/// 覆盖场景：P-001 指定时段进入指定区域, P-002 个人通行记录, P-007 异常时段人员
/// </summary>
public class GetPersonnelAccessTool : IQueryTool
{
    private readonly IDatabaseExecutor _executor;
    private readonly ILogger<GetPersonnelAccessTool> _logger;

    public string Name => "get_personnel_access";
    public string Description => "查询人员通过闸机的进出记录，支持按姓名、学工号、时间范围、位置筛选。用于回答'谁进了某区域'、'某人的进出记录'、'深夜进出人员'等问题。";

    public GetPersonnelAccessTool(
        IDatabaseExecutor executor,
        ILogger<GetPersonnelAccessTool> logger)
    {
        _executor = executor;
        _logger = logger;
    }

    public bool ValidateParameters(Dictionary<string, object> parameters, out List<string> errors)
    {
        errors = new List<string>();
        return true;
    }

    public async Task<ToolResult> ExecuteAsync(Dictionary<string, object> parameters, CancellationToken cancellationToken = default)
    {
        if (parameters == null)
        {
            return new ToolResult
            {
                Success = false,
                Errors = new List<string> { "参数不能为空" }
            };
        }

        try
        {
            // 获取参数
            string? personName = parameters.ContainsKey("personName") ? parameters["personName"]?.ToString() : null;
            string? personId = parameters.ContainsKey("personId") ? parameters["personId"]?.ToString() : null;
            string? startTime = parameters.ContainsKey("startTime") ? parameters["startTime"]?.ToString() : null;
            string? endTime = parameters.ContainsKey("endTime") ? parameters["endTime"]?.ToString() : null;
            string? location = parameters.ContainsKey("location") ? parameters["location"]?.ToString() : null;
            int? direction = parameters.ContainsKey("direction") ? Convert.ToInt32(parameters["direction"]) : null; // 1=进, 0=出
            bool abnormalTime = parameters.ContainsKey("abnormalTime") && Convert.ToBoolean(parameters["abnormalTime"]); // 是否查询异常时段（深夜）

            // 构建SQL
            var sql = BuildQuery(personName, personId, startTime, endTime, location, direction, abnormalTime);

            _logger.LogInformation("Executing personnel access query with SQL: {Sql}", sql);

            var result = await _executor.ExecuteQueryAsync(sql, null, cancellationToken);

            return new ToolResult
            {
                Success = true,
                Data = result.Data,
                Message = "人员进出记录查询成功",
                Metadata = new Dictionary<string, object>
                {
                    ["executionTimeMs"] = result.ExecutionTimeMs,
                    ["executedSql"] = sql
                }
            };
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Error executing personnel access query");
            return new ToolResult
            {
                Success = false,
                Errors = new List<string> { $"查询失败: {ex.Message}" }
            };
        }
    }

    private string BuildQuery(string? personName, string? personId, string? startTime, string? endTime,
                              string? location, int? direction, bool abnormalTime)
    {
        var sb = new StringBuilder();

        sb.AppendLine("SELECT");
        sb.AppendLine("    XGH AS student_id,");
        sb.AppendLine("    XM AS name,");
        sb.AppendLine("    DATE_FORMAT(SJ, '%Y-%m-%d %H:%i:%s') AS pass_time,");
        sb.AppendLine("    XQMC AS campus,");
        sb.AppendLine("    ZJMC AS gate_name,");
        sb.AppendLine("    CASE CRBS");
        sb.AppendLine("        WHEN 1 THEN '进'");
        sb.AppendLine("        WHEN 0 THEN '出'");
        sb.AppendLine("        ELSE '未知'");
        sb.AppendLine("    END AS direction,");
        sb.AppendLine("    ZTMC AS status,");
        sb.AppendLine("    ZTM AS status_code");
        sb.AppendLine("FROM TblGateInformation");
        sb.AppendLine("WHERE 1=1");

        // 姓名或学工号筛选
        if (!string.IsNullOrEmpty(personName) || !string.IsNullOrEmpty(personId))
        {
            sb.Append("    AND (");
            var conditions = new List<string>();
            if (!string.IsNullOrEmpty(personName))
            {
                conditions.Add($"XM = '{personName}' OR XM LIKE '%{personName}%'");
            }
            if (!string.IsNullOrEmpty(personId))
            {
                conditions.Add($"XGH = '{personId}'");
            }
            sb.AppendLine(string.Join(" OR ", conditions) + ")");
        }

        // 时间范围筛选
        if (!string.IsNullOrEmpty(startTime))
        {
            sb.AppendLine($"    AND SJ >= '{startTime}'");
        }
        if (!string.IsNullOrEmpty(endTime))
        {
            sb.AppendLine($"    AND SJ <= '{endTime}'");
        }

        // 位置筛选
        if (!string.IsNullOrEmpty(location))
        {
            sb.AppendLine($"    AND ZJMC LIKE '%{location}%'");
        }

        // 进出方向筛选
        if (direction.HasValue)
        {
            sb.AppendLine($"    AND CRBS = {direction.Value}");
        }

        // 异常时段筛选（深夜：22:00-06:00）
        if (abnormalTime)
        {
            sb.AppendLine("    AND (HOUR(SJ) >= 22 OR HOUR(SJ) < 6)");
        }

        sb.AppendLine("ORDER BY SJ DESC");
        sb.AppendLine("LIMIT 100");

        return sb.ToString();
    }
}
